package com.label.Service.Impl;

import cn.hutool.core.bean.BeanUtil;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.google.common.collect.ImmutableMap;
import com.label.Dao.HelpRecordMapper;
import com.label.Dao.SemeWarnMapper;
import com.label.Dao.UserMapper;
import com.label.Dao.WarnQuesMapper;
import com.label.Model.Do.HelpRecord;
import com.label.Model.Do.SemeWarn;
import com.label.Model.Do.User;
import com.label.Model.Do.WarnQues;
import com.label.Model.Query.HelpRecordQuery;
import com.label.Model.Query.WarnQuesQuery;
import com.label.Model.Query.WarningQuery;
import com.label.Model.Vo.SemeWarnVo;
import com.label.Service.WarningService;
import com.label.Utils.Common.InfoUtil;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.*;
import java.util.stream.Collectors;

@Service
public class WarningServiceImpl implements WarningService {

    @Resource
    @Qualifier("oracleJdbcTemplate")
    private JdbcTemplate oracleJdbc;

    @Resource
    private WarnQuesMapper warnQuesMapper;

    @Resource
    private SemeWarnMapper semeWarnMapper;

    @Resource
    private HelpRecordMapper helpRecordMapper;

    @Resource
    private UserMapper userMapper;

    public Map<Integer,String> level= ImmutableMap.of(1,"三级预警",2,"二级预警",3,"一级预警");

    @Override
    public String warningStuSearch(WarningQuery warningQuery) {
        String sql="select USERID,COURSEID,EXAMCOURSENAME,MAX(YEARANDSEME) as YEARANDSEME from (select USERID,COURSEID,YEARANDSEME,EXAMCOURSENAME from STUDENTEXAM where TWARNTAG = 1 ${userid}  ${nameOrClass}) GROUP BY USERID,COURSEID,EXAMCOURSENAME";
        if(warningQuery.getUserId()!=null){
            sql=sql.replace("${userid}","and USERID='"+warningQuery.getUserId()+"'");
        }
        else
            sql=sql.replace("${userid}","");
        if(warningQuery.getName()!=null||warningQuery.getClassId()!=null){
            String part="and USERID in (SELECT USERID from STUDENTPROFILE WHERE ${userName} and ${classId})";
            if(warningQuery.getName()!=null){
                part=part.replace("${userName}","USERNAME like '%"+warningQuery.getName()+"%'");
            }
            else
                part=part.replace("${userName}","1=1");
            if(warningQuery.getClassId()!=null){
                part=part.replace("${classId}","CLASSID = '"+warningQuery.getClassId()+"'");
            }
            else
                part=part.replace("${classId}","1=1");

            sql=sql.replace("${nameOrClass}",part);
        }
        else
            sql=sql.replace("${nameOrClass}","");


        //学号，课程编号，课程名
        List<Map<String,Object>> resData=oracleJdbc.queryForList(sql);


        return to_Vo(resData);
    }

    @Override
    public String warningStuDetail(WarningQuery warningQuery) {
        String warnSql="select * from SEME_WARN where USERID ='"+warningQuery.getUserId()+"'";
        List<Map<String,Object>> warnList=oracleJdbc.queryForList(warnSql);

        Map<String,Object> detailInfo=new HashMap<>();
        SemeWarn semeWarn=new SemeWarn();
        Map<String,Object> helpInfo=new HashMap<>();
        List<Map<String,Object>> warnData=new ArrayList<>();

        if(warnList.size()>0){
            Map<Integer,List<Map<String,Object>>> warnGroup=warnList.stream().collect(Collectors.groupingBy(p->Integer.parseInt(p.get("WARN_YEAR").toString())));
            Integer year = warnGroup.keySet().stream().max((a,b)->{if(a>b)return 1; else return -1;}).get();
            if(warnGroup.get(year).size()==1||warnGroup.get(year).get(0).get("WARN_SEASON").toString().equals("秋季学期")){
                warningQuery.setSemeWarnId(warnGroup.get(year).get(0).get("SEME_WARN_ID").toString());
            }else{
                    warningQuery.setSemeWarnId(warnGroup.get(year).get(1).get("SEME_WARN_ID").toString());
            }

            String detailInfoSql="select * from SEME_WARN where SEME_WARN_ID="+warningQuery.getSemeWarnId();
            detailInfo=oracleJdbc.queryForMap(detailInfoSql);
            semeWarn=semeWarnMapper.selectById(warningQuery.getSemeWarnId());

            //帮扶人信息
            if(detailInfo.get("HELP_USERID")!=null){
                String helpSql="select * from STUDENTPROFILE where USERID='"+detailInfo.get("HELP_USERID").toString()+"'";
                List<Map<String,Object>> helpInfoList=oracleJdbc.queryForList(helpSql);
                if(helpInfoList.size()!=0){
                    helpInfo.put("userid",helpInfoList.get(0).get("USERID"));
                    helpInfo.put("sex",helpInfoList.get(0).get("SEX").toString().equals("1")?"男":"女");
                    helpInfo.put("class",InfoUtil.getClassName(helpInfoList.get(0).get("CLASSID").toString()));
                    helpInfo.put("enrollyear",helpInfoList.get(0).get("ENROLLYEAR"));
                    helpInfo.put("politics",helpInfoList.get(0).get("POLITICS"));
                }
            }

            //预警详情
            String sql="select COURSEID,EXAMCOURSENAME,TIME,CREDIT from STUDENTEXAM where userid='"+detailInfo.get("USERID")+"' and swarntag = 1 ";
            if(detailInfo.get("WARN_SEASON").toString().equals("春季学期")){
                sql=sql+" and TIME>'"+Integer.parseInt(detailInfo.get("WARN_YEAR").toString())+"0301' and TIME<='"+Integer.parseInt(detailInfo.get("WARN_YEAR").toString())+"0901'";
            }
            else{
                sql=sql+" and TIME>'"+Integer.parseInt(detailInfo.get("WARN_YEAR").toString())+"0901' and TIME<='"+(Integer.parseInt(detailInfo.get("WARN_YEAR").toString())+1)+"0301'";
            }
            warnData=oracleJdbc.queryForList(sql);
        }








        //累计详情
        String Stilsql="select USERID,COURSEID,EXAMCOURSENAME,MAX(YEARANDSEME) as SEME,MAX(TIME) as TIME from (select USERID,COURSEID,YEARANDSEME,EXAMCOURSENAME,TIME from STUDENTEXAM where TWARNTAG = 1 and USERID='"+warningQuery.getUserId()+"' ) GROUP BY USERID,COURSEID,EXAMCOURSENAME";
        List<Map<String,Object>> stilData=oracleJdbc.queryForList(Stilsql);

        //帮扶记录
        List<HelpRecord> helpRecords=helpRecordMapper.selectList(new QueryWrapper<HelpRecord>().lambda().eq(HelpRecord::getHelpUserId,warningQuery.getUserId()).or().eq(HelpRecord::getWarnUserId,warningQuery.getUserId()));


        //辅导员信息
        String teacherSql="select * from STUDENTPROFILE where USERID='"+warningQuery.getUserId()+"'";
        List<Map<String,Object>> teacherInfo=oracleJdbc.queryForList(teacherSql);
        Map<String, Object> teacher=null;
        if(teacherInfo.size()!=0) {
            List<Map<String, Object>> teacherDetail = oracleJdbc.queryForList("select USERNAME,ACCOUNT from SYSUSER where USERNAME='" + teacherInfo.get(0).get("COUNSELORID")+"'");
            if(teacherDetail.size()!=0){
                teacher = ImmutableMap.of("name", teacherDetail.get(0).get("USERNAME"), "id", teacherDetail.get(0).get("ACCOUNT"));
            }
        }


        Map<String,Object> res=new HashMap<>();
        res.put("warn",warnData);
        res.put("stil",stilData);
        res.put("info",semeWarn);
        res.put("helpRecords",helpRecords);
        res.put("helpInfo",helpInfo);
        res.put("teacher",teacher);

        return JSONObject.toJSONString(res);
    }

    @Override
    public Boolean questWarn(WarnQuesQuery warnQuesQuery) {
        //受理人，预警记录iD，课程编号，考试日期，课程名称，预警问题类型
        SemeWarn semeWarn=semeWarnMapper.selectById(warnQuesQuery.getSemeQuesId());
        List<Map<String,Object>> classInfo=oracleJdbc.queryForList("select * from studentattr where userid='"+semeWarn.getUserId()+"'");
        WarnQues warnQues=new WarnQues(semeWarn.getUserId(),warnQuesQuery.getAcceptId(),warnQuesQuery.getCourseId(),warnQuesQuery.getCourseName(),warnQuesQuery.getCourseTime(),InfoUtil.getClassName(classInfo.get(0).get("CLASSID").toString()),"1",warnQuesQuery.getQuesType(),warnQuesQuery.getQuesType().equals("0")?Long.valueOf(warnQuesQuery.getSemeQuesId()):null);
        warnQuesMapper.insert(warnQues);
        return true;
    }

    @Override
    public Boolean semeWarn(String year, String seme) {
        //学期预警
        String semeWarnSql="UPDATE STUDENTEXAM SET SWARNTAG = 1 WHERE ( USERID, COURSEID, TIME ) IN (SELECT USERID, COURSEID,TIME from (SELECT USERID, COURSEID,MAX(SCORE) as MAXSCORE,MIN( TIME ) as TIME FROM (SELECT * from STUDENTEXAM where ${time}) GROUP BY USERID,COURSEID) WHERE MAXSCORE < 60 ) ";
        String timeSql="";

        if(seme.equals("1"))
            timeSql=" TIME>'"+year+"0301' and TIME<='"+year+"0901'";
        else
            timeSql=" TIME>'"+year+"0901' and TIME<='"+(Integer.parseInt(year)+1)+"0301'";

        semeWarnSql=semeWarnSql.replace("${time}",timeSql);
        oracleJdbc.execute(semeWarnSql);

        String warnSql="select USERID,count(*) as course from (select USERID,COURSEID from studentexam where SWARNTAG=1 and "+timeSql+") group by userid";
        List<Map<String,Object>> userList=oracleJdbc.queryForList(warnSql);



        userList.forEach(p->{
            SemeWarn semeWarn=new SemeWarn(p.get("USERID").toString(), level.get(Math.min(Integer.parseInt(p.get("COURSE").toString()), 3)),Long.valueOf(year),seme.equals("1")?"春季学期":"秋季学期",Long.valueOf(p.get("COURSE").toString()));
            semeWarnMapper.insert(semeWarn);
        });

        return true;
    }

    @Override
    public Boolean stilWarn(String year, String seme) {
        //累计挂科 ,去掉已经通过的科目
        String sql="UPDATE STUDENTEXAM t1 set t1.TWARNTAG = 0  where exists (select 1 from (select userid,COURSEID from STUDENTEXAM where ${time} and  score >= 60) t2  where t1.USERID=t2.USERID and t1.COURSEID=t2.COURSEID)";
        String timeSql="";
        if(seme.equals("1"))
            timeSql=" TIME>'"+year+"0301' and TIME<'"+year+"0901'";
        else
            timeSql=" TIME>'"+year+"0901' and TIME<'"+(Integer.parseInt(year)+1)+"0301'";

        sql=sql.replace("${time}",timeSql);
        oracleJdbc.execute(sql);

        String newFail="UPDATE STUDENTEXAM t1 set t1.TWARNTAG = 1 where exists (select 1 from (select userid,COURSEID from STUDENTEXAM where ${time} and  score < 60 ) t2 where t1.USERID=t2.USERID and t1.COURSEID=t2.COURSEID)";
        newFail=newFail.replace("${time}",timeSql);
        oracleJdbc.execute(newFail);

        return true;
    }

    @Override
    public Boolean addHelpRecord(HelpRecordQuery helpRecordQuery) {
        SemeWarn semeWarn=semeWarnMapper.selectById(helpRecordQuery.getSemeWarnId());

        User user=userMapper.selectOne(new QueryWrapper<User>().lambda().eq(User::getAccount,semeWarn.getUserId()));
        User helpUser=userMapper.selectOne(new QueryWrapper<User>().lambda().eq(User::getAccount,semeWarn.getHelpUserId()));

        HelpRecord helpRecord=new HelpRecord(user.getAccount(),user.getUserName(),helpUser.getAccount(),helpUser.getUserName(),helpRecordQuery.getSemeWarnId(), helpRecordQuery.getHelpDesc());
        helpRecordMapper.insert(helpRecord);
        return true;
    }

    @Override
    public Map<String,Object> getMyHelpSemeWarn(WarningQuery warningQuery) {
//        List<SemeWarn> helpSemeWarn=semeWarnMapper.selectList(new QueryWrapper<SemeWarn>().lambda().eq(SemeWarn::getHelpUserId,warningQuery.getUserId()));
//        if(helpSemeWarn.size()>0) {
//            Map<String, List<SemeWarn>> warnGroup = helpSemeWarn.stream().collect(Collectors.groupingBy(p -> p.getWarnYear()+"_"+p.getWarnSeason()));
//            String yearAndSeme = warnGroup.keySet().stream().max((a, b) -> {
//                String[] aT=a.split("_");
//                String[] bT=b.split("_");
//                if (Integer.parseInt(aT[0]) > Integer.parseInt(bT[0]))
//                    return 1;
//                else
//                    if(Integer.parseInt(aT[0]) < Integer.parseInt(bT[0]))
//                        return -1;
//                    else
//                        if(aT[0].equals(bT[0]))
//                            return 0;
//                        else if(aT[0].equals("春季学期"))
//                            return -1;
//                        else
//                            return 1;
//            }).get();
//            System.out.println(yearAndSeme);
//            return warnGroup.get(yearAndSeme);
//        }
//        return new ArrayList<>();
        List<SemeWarn> helpWarns=semeWarnMapper.selectList(new QueryWrapper<SemeWarn>().lambda().eq(SemeWarn::getHelpUserId,warningQuery.getUserId()));
        List<SemeWarnVo> helpWarnVos= helpWarns.stream().map((entity) -> {
            return BeanUtil.copyProperties(entity, SemeWarnVo.class);
        }).collect(Collectors.toList());


        List<SemeWarn> getWarns=semeWarnMapper.selectList(new QueryWrapper<SemeWarn>().lambda().eq(SemeWarn::getUserId,warningQuery.getUserId()).isNotNull(SemeWarn::getHelpUserId));
        List<SemeWarnVo> getWarnVos= getWarns.stream().map((entity) -> {
            return BeanUtil.copyProperties(entity, SemeWarnVo.class);
        }).collect(Collectors.toList());

        return ImmutableMap.of("helper",helpWarnVos,"warner",getWarnVos);
    }

    private String to_Vo(List<Map<String,Object>> resData){
        Map<String,List<Map<String,Object>>> resGroup=resData.stream().collect(Collectors.groupingBy(p->p.get("USERID").toString()));
        List<Map<String,Object>> resultData=new ArrayList<>();

        resGroup.forEach((k,v)->{
            List<Map<String,Object>> userInfo=oracleJdbc.queryForList("select * from STUDENTPROFILE where USERID='"+k+"'");
            if(userInfo.size()>0){
                Map<String,Object> resDataPart=new HashMap<>();
                resDataPart.put("userId",k);
                resDataPart.put("userName",userInfo.get(0).get("USERNAME"));
                resDataPart.put("className", InfoUtil.getClassName(userInfo.get(0).get("CLASSID").toString()));
                resDataPart.put("count",v.size());
                resDataPart.put("detail",v);
                resultData.add(resDataPart);
            }

        });

        return JSONObject.toJSONString(resultData);
    }

}
